package cdu.notFound.Dao.impl;


import cdu.notFound.Dao.GoodsDao;
import cdu.notFound.model.Goods;
import cdu.notFound.utils.DatabaseConnection;
import cdu.notFound.utils.IntHolder;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class GoodsDaoImpl implements GoodsDao {
    private Connection conn = null;
    private PreparedStatement pstmt = null;
    public GoodsDaoImpl() {
        try {
            this.conn = new DatabaseConnection().getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public Goods findById(int id) {
        Goods goods = null;
        String sql = "SELECT id,image,type_id,name,num,price,status,content,producter_id,create_date FROM goods WHERE id=?";
        try {
            this.pstmt = this.conn.prepareStatement(sql);
            this.pstmt.setInt(1, id);
            ResultSet rs = this.pstmt.executeQuery();
            if (rs.next()) {
                goods = new Goods();
                goods.setId(rs.getInt(1));
                goods.setImage(rs.getString(2));
                goods.setType_id(rs.getInt(3));
                goods.setName(rs.getString(4));
                goods.setNum(rs.getInt(5));
                goods.setPrice(rs.getFloat(6));
                goods.setStates(rs.getInt(7));
                goods.setContent(rs.getString(8));
                goods.setProducter_id(rs.getInt(9));
                Timestamp timeStamp=rs.getTimestamp(10);
                Date date=new Date(timeStamp.getTime());
                goods.setCreatDate(date);
            }
            rs.close();this.pstmt.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return goods;
    }

    public boolean doCreate(Goods good) {
        boolean flag = false;
        String sql = "INSERT INTO `goods`(name,price,image,content,status,type_id,producter_id,create_date,num) VALUES (?,?,?,?,?,?,?,?,?)";
        try {
            this.pstmt = this.conn.prepareStatement(sql);
            pstmt.setString(1, good.getName());
            pstmt.setFloat(2, good.getPrice());
            pstmt.setString(3, good.getImage());
            pstmt.setString(4, good.getContent());
            pstmt.setInt(5, 1);
            pstmt.setInt(6, good.getType_id());
            pstmt.setInt(7, good.getProducter_id());
            Timestamp dateTime = new java.sql.Timestamp(good.getCreatDate().getTime());
            pstmt.setTimestamp(8, dateTime);
            pstmt.setInt(9, good.getNum());
            if (this.pstmt.executeUpdate() > 0) {
                flag = true;
            }
            this.pstmt.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return flag;
    }

    public int getMaxId() {
        ResultSet rs = null;
        int maxId = 0;
        try {
            rs = this.conn.prepareStatement("select max(id) from goods").executeQuery();
            rs.next();
            maxId = rs.getInt(1);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return maxId;
    }

    public List<Goods> findAll(IntHolder num, int limitMin, int perPage) {
        List<Goods> goods = new ArrayList<Goods>();
        String sql = "SELECT SQL_CALC_FOUND_ROWS id,num,content,type_id,image,producter_id,price,create_date,name from goods  where status=? order by create_date desc limit ?,?";
        try {
            this.pstmt = this.conn.prepareStatement(sql);
            this.pstmt.setInt(1, 2);
            this.pstmt.setInt(2, limitMin);
            this.pstmt.setInt(3, perPage);
            ResultSet rs = this.pstmt.executeQuery();
            while (rs.next()) {
                Goods good = new Goods();
                good.setId(rs.getInt(1));
                good.setNum(rs.getInt(2));
                good.setContent(rs.getString(3));
                good.setType_id(rs.getInt(4));
                good.setImage(rs.getString(5));
                good.setProducter_id(rs.getInt(6));
                good.setPrice(rs.getFloat(7));
                Timestamp timeStamp=rs.getTimestamp(8);
                good.setName(rs.getString(9));
                Date date=new  Date(timeStamp.getTime());
                good.setCreatDate(date);
                goods.add(good);
            }
            rs = this.pstmt.executeQuery("SELECT FOUND_ROWS()");
            if(rs.next()){
                num.value=rs.getInt(1);
            }
            rs.close();this.pstmt.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return goods;
    }

    public List<Goods> findAllNotAuditing(){
        List<Goods> goods = new ArrayList<Goods>();
        String sql = "SELECT id,num,content,type_id,image,producter_id,price,name,create_date from goods where status=1";
        try {
            this.pstmt = this.conn.prepareStatement(sql);
            ResultSet rs = this.pstmt.executeQuery();
            Goods good = null;
            while (rs.next()) {
                good = new Goods();
                good.setId(rs.getInt(1));
                good.setNum(rs.getInt(2));
                good.setContent(rs.getString(3));
                good.setType_id(rs.getInt(4));
                good.setImage(rs.getString(5));
                good.setProducter_id(rs.getInt(6));
                good.setPrice(rs.getFloat(7));
                good.setName(rs.getString(8));
                Timestamp timeStamp=rs.getTimestamp(9);
                Date date=new Date(timeStamp.getTime());
                good.setCreatDate(date);
                goods.add(good);
            }
            rs.close();this.pstmt.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return goods;
    }

    public boolean doUpdate(Goods good){
        boolean flag = false;
        String sql = "update goods set image=?,type_id=?,name=?,num=?,price=?,status=?,content=?,producter_id=?,create_date=? where id=?";
        try {
            this.pstmt = this.conn.prepareStatement(sql);
            pstmt.setString(1, good.getImage());
            pstmt.setInt(2, good.getType_id());
            pstmt.setString(3, good.getName());
            pstmt.setInt(4, good.getNum());
            pstmt.setFloat(5, good.getPrice());
            pstmt.setInt(6, good.getStates());
            pstmt.setString(7, good.getContent());
            pstmt.setInt(8, good.getProducter_id());
            java.sql.Timestamp dateTime = new java.sql.Timestamp(good.getCreatDate().getTime());
            pstmt.setTimestamp(9, dateTime);
            pstmt.setInt(10, good.getId());
            if (this.pstmt.executeUpdate() > 0) {
                flag = true;
            }
            this.pstmt.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return flag;
    }

    public List<Goods> findByCeta(int cetaId,IntHolder num,int limitMin,int perPage){
        List<Goods> all = new ArrayList<Goods>();
        String sql = "SELECT SQL_CALC_FOUND_ROWS id,num,content,type_id,image,producter_id,price,name,create_date from goods where status=2 and type_id="+cetaId+" order by create_date desc limit ?,?";
        try {
            this.pstmt = this.conn.prepareStatement(sql);
            this.pstmt.setInt(1, limitMin);
            this.pstmt.setInt(2, perPage);
            ResultSet rs = this.pstmt.executeQuery();
            Goods good = null;
            while (rs.next()){
                good = new Goods();
                good.setId(rs.getInt(1));
                good.setNum(rs.getInt(2));
                good.setContent(rs.getString(3));
                good.setType_id(rs.getInt(4));
                good.setImage(rs.getString(5));
                good.setProducter_id(rs.getInt(6));
                good.setPrice(rs.getFloat(7));
                good.setName(rs.getString(8));
                Timestamp timeStamp=rs.getTimestamp(9);
                Date date=new Date(timeStamp.getTime());
                good.setCreatDate(date);
                all.add(good);
            }
            rs = this.pstmt.executeQuery("SELECT FOUND_ROWS()");
            if(rs.next()){
                num.value=rs.getInt(1);
            }
            rs.close();this.pstmt.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return all;
    }
    
    public List<Goods> findByKey(String key) {
        List<Goods> all = new ArrayList<Goods>();
        String sql = "SELECT id,num,content,type_id,image,producter_id,price,name,create_date from goods where status=2 and name like ? order by create_date desc";
        try {
            this.pstmt = this.conn.prepareStatement(sql);
            this.pstmt.setString(1, "%"+key+"%");
            ResultSet rs = this.pstmt.executeQuery();
            Goods good = null;
            while (rs.next()){
                good = new Goods();
                good.setId(rs.getInt(1));
                good.setNum(rs.getInt(2));
                good.setContent(rs.getString(3));
                good.setType_id(rs.getInt(4));
                good.setImage(rs.getString(5));
                good.setProducter_id(rs.getInt(6));
                good.setPrice(rs.getFloat(7));
                good.setName(rs.getString(8));
                Timestamp timeStamp=rs.getTimestamp(9);
                Date date=new Date(timeStamp.getTime());
                good.setCreatDate(date);
                all.add(good);
            }
            rs.close();this.pstmt.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return all;
    }
    
    public List<Goods> findByUserId(int userId) {
        List<Goods> all = new ArrayList<Goods>();
        String sql = "SELECT id,num,content,type_id,image,producter_id,price,name,create_date,status from goods where (status=2 or status=4) and producter_id="+userId+" order by create_date desc";
        try {
            this.pstmt = this.conn.prepareStatement(sql);
            ResultSet rs = this.pstmt.executeQuery();
            Goods good = null;
            while (rs.next()){
                good = new Goods();
                good.setId(rs.getInt(1));
                good.setNum(rs.getInt(2));
                good.setContent(rs.getString(3));
                good.setType_id(rs.getInt(4));
                good.setImage(rs.getString(5));
                good.setProducter_id(rs.getInt(6));
                good.setPrice(rs.getFloat(7));
                good.setName(rs.getString(8));
                Timestamp timeStamp=rs.getTimestamp(9);
                Date date=new Date(timeStamp.getTime());
                good.setCreatDate(date);
                good.setStates(rs.getInt(10));
                all.add(good);
            }
            rs.close();this.pstmt.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return all;
    }
    
	public void close(){
		if(this.conn != null){
			try{
				this.conn.close();
			}catch(Exception e){
				e.printStackTrace();
			}
		}
	}
}
